package com.flash.ReportManagement.mapper;

import com.flash.ReportManagement.entity.RollDifference;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


@Mapper
public interface RollDifferenceMapper {

    @Select("select \n" +
            "(case exchangeName when 1 then '上海' else '深圳' end)||\n" +
            "(case securityType when 1 then '债卷' else '股票' end)as securitiesName,\n" +
            "nvl(sum(case dealWay when 1 then endMoney*dealWay end),0)as outMoney,\n" +
            "nvl(sum(case dealWay when 2 then endMoney*1 end),0)as inMoney,\n" +
            "nvl(sum(case dealWay when 2 then endMoney*1 end),0)-nvl(sum(case dealWay when 1 then endMoney*dealWay end),0)as amountToday,\n" +
            "nvl(sum(handle),0)as handle,\n" +
            "nvl(sum(stampDuty),0)as stampDuty,\n" +
            "nvl(sum(transferFee),0)as transferFee,\n" +
            "nvl(sum(commissionFees),0)as commissionFees,\n" +
            "nvl(sum(case dealWay when 2 then endMoney*1 end),0)-nvl(sum(case dealWay when 1 then endMoney*dealWay end),0)\n" +
            "-nvl(sum(handle),0)-nvl(sum(stampDuty),0)-nvl(sum(transferFee),0)-nvl(sum(commissionFees),0)\n" +
            "as netReceipts\n" +
            "from \n" +
            "(select * from dealData)d\n" +
            "join\n" +
            "(select * from security)s\n" +
            "on \n" +
            "d.securityId=s.securityId\n" +
            "where dealTime=#{dealTime} group by exchangeName,securityType\n" +
            "union all\n" +
            "select '两地合计' as securitiesName,\n" +
            "nvl(sum(case dealWay when 1 then endMoney*dealWay end),0)as outMoney,\n" +
            "nvl(sum(case dealWay when 2 then endMoney*1 end),0)as inMoney,\n" +
            "nvl(sum(case dealWay when 2 then endMoney*1 end),0)-nvl(sum(case dealWay when 1 then endMoney*dealWay end),0)as amountToday,\n" +
            "nvl(sum(handle),0)as handle,\n" +
            "nvl(sum(stampDuty),0)as stampDuty,\n" +
            "nvl(sum(transferFee),0)as transferFee,\n" +
            "nvl(sum(commissionFees),0)as commissionFees,\n" +
            "nvl(sum(case dealWay when 2 then endMoney*1 end),0)-nvl(sum(case dealWay when 1 then endMoney*dealWay end),0)\n" +
            "-nvl(sum(handle),0)-nvl(sum(stampDuty),0)-nvl(sum(transferFee),0)-nvl(sum(commissionFees),0)\n" +
            "from dealData where dealTime=#{dealTime}")
    public List<RollDifference> selectRollDifference(String dealTime);
}
